const mysql = require('mysql2/promise');

// 数据库配置
const dbConfig = {
    host: '43.143.253.188',
    port: 3306,
    user: 'nextjs',
    password: 'yEAiRE2jcxTyRBy5',
    database: 'nextjs',
    charset: 'utf8mb4'
};

async function checkGemTables() {
    let connection;

    try {
        console.log('🔍 检查宝石系统数据库表格...');

        // 连接数据库
        connection = await mysql.createConnection(dbConfig);
        console.log('✅ 数据库连接成功');

        // 检查所有宝石相关表
        const gemTables = [
            'gem_base',
            'character_gem_inventory',
            'equipment_gem_socket',
            'gem_mining'
        ];

        console.log('\n📊 宝石系统表格检查结果:');
        console.log('='.repeat(50));

        for (const tableName of gemTables) {
            try {
                // 检查表是否存在
                const [tables] = await connection.execute(`
          SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
          FROM information_schema.TABLES 
          WHERE TABLE_SCHEMA = 'nextjs' AND TABLE_NAME = ?
        `, [tableName]);

                if (tables.length > 0) {
                    const table = tables[0];
                    console.log(`\n✅ ${tableName} 表存在`);
                    console.log(`   - 行数: ${table.TABLE_ROWS || 0}`);
                    console.log(`   - 数据大小: ${Math.round((table.DATA_LENGTH || 0) / 1024)} KB`);
                    console.log(`   - 索引大小: ${Math.round((table.INDEX_LENGTH || 0) / 1024)} KB`);

                    // 检查表结构
                    const [columns] = await connection.execute(`DESCRIBE ${tableName}`);
                    console.log(`   - 字段数: ${columns.length}`);

                    // 检查数据
                    const [data] = await connection.execute(`SELECT COUNT(*) as count FROM ${tableName}`);
                    console.log(`   - 数据条数: ${data[0].count}`);

                    // 如果是gem_base表，显示宝石类型
                    if (tableName === 'gem_base' && data[0].count > 0) {
                        const [gems] = await connection.execute(`
              SELECT name, type, description 
              FROM ${tableName} 
              ORDER BY type
            `);
                        console.log(`   - 宝石类型:`);
                        gems.forEach(gem => {
                            console.log(`     ${gem.type}. ${gem.name}: ${gem.description}`);
                        });
                    }

                    // 如果是gem_mining表，显示开矿配置
                    if (tableName === 'gem_mining' && data[0].count > 0) {
                        const [mining] = await connection.execute(`
              SELECT name, key_item_name, min_level, max_level 
              FROM ${tableName} 
              ORDER BY type
            `);
                        console.log(`   - 开矿配置:`);
                        mining.forEach(config => {
                            console.log(`     ${config.name}: 需要${config.key_item_name}, 获得${config.min_level}-${config.max_level}级宝石`);
                        });
                    }

                } else {
                    console.log(`\n❌ ${tableName} 表不存在`);
                }

            } catch (error) {
                console.log(`\n❌ 检查 ${tableName} 表时出错: ${error.message}`);
            }
        }

        // 检查外键约束
        console.log('\n🔗 外键约束检查:');
        console.log('='.repeat(50));

        const [foreignKeys] = await connection.execute(`
      SELECT 
        TABLE_NAME,
        COLUMN_NAME,
        CONSTRAINT_NAME,
        REFERENCED_TABLE_NAME,
        REFERENCED_COLUMN_NAME
      FROM information_schema.KEY_COLUMN_USAGE 
      WHERE TABLE_SCHEMA = 'nextjs' 
      AND REFERENCED_TABLE_NAME IS NOT NULL
      AND TABLE_NAME IN ('character_gem_inventory', 'equipment_gem_socket')
      ORDER BY TABLE_NAME, COLUMN_NAME
    `);

        if (foreignKeys.length > 0) {
            foreignKeys.forEach(fk => {
                console.log(`✅ ${fk.TABLE_NAME}.${fk.COLUMN_NAME} -> ${fk.REFERENCED_TABLE_NAME}.${fk.REFERENCED_COLUMN_NAME}`);
            });
        } else {
            console.log('❌ 未找到外键约束');
        }

        // 检查索引
        console.log('\n📇 索引检查:');
        console.log('='.repeat(50));

        for (const tableName of gemTables) {
            try {
                const [indexes] = await connection.execute(`
          SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
          FROM information_schema.STATISTICS 
          WHERE TABLE_SCHEMA = 'nextjs' AND TABLE_NAME = ?
          ORDER BY INDEX_NAME, SEQ_IN_INDEX
        `, [tableName]);

                if (indexes.length > 0) {
                    console.log(`\n📋 ${tableName} 表索引:`);
                    const indexGroups = {};
                    indexes.forEach(idx => {
                        if (!indexGroups[idx.INDEX_NAME]) {
                            indexGroups[idx.INDEX_NAME] = [];
                        }
                        indexGroups[idx.INDEX_NAME].push(idx.COLUMN_NAME);
                    });

                    Object.entries(indexGroups).forEach(([indexName, columns]) => {
                        const type = indexName === 'PRIMARY' ? '主键' :
                            indexName.includes('uk_') ? '唯一索引' : '普通索引';
                        console.log(`   - ${indexName} (${type}): ${columns.join(', ')}`);
                    });
                }
            } catch (error) {
                console.log(`❌ 检查 ${tableName} 索引时出错: ${error.message}`);
            }
        }

        console.log('\n🎉 宝石系统数据库检查完成！');

    } catch (error) {
        console.error('❌ 检查失败:', error);
        throw error;
    } finally {
        if (connection) {
            await connection.end();
            console.log('🔌 数据库连接已关闭');
        }
    }
}

// 运行检查
checkGemTables().catch(console.error);
